﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;

namespace BEGDC.Utility.DataAccess
{
    public sealed  class SQLServer
    {
        

        #region DoubleQuote
        /// <summary>
        /// 
        /// </summary>
        /// <param name="SomeString"></param>
        /// <returns></returns>
        public static string DoubleQuote(object value)
        {
            if (value == null)
            {
                return null;
            }
            else
            {
                return value.ToString().Replace("'", "''");
            }
        }
        public static string FormatNumber(object value)
        {
            if (value == null)
            {
                return null;
            }
            else
            {
                return value.ToString().Replace(",", "");
            }
        }
        #endregion

        #region FormatStringToHtml
        public static string FormatStringToHtml(string SomeString)
        {
            SomeString = DoubleQuote(SomeString);
            SomeString = SomeString.Replace("\r\n", "<br>");
            SomeString = SomeString.Replace(" ", "&nbsp;");
            return SomeString;
        }
        #endregion

        #region NewGuid
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static string NewGuid()
        {
            return Guid.NewGuid().ToString().ToUpper();
        }
        #endregion

        #region ExecuteDataset
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string constr, string commandText, SqlParameter[] commandParameters)
        {
            DataSet ds = SqlHelper.ExecuteDataset(constr, CommandType.Text, commandText, commandParameters);
            ds.Locale = new System.Globalization.CultureInfo("zh-cn");
            return ds;
        }
   
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteDatasetBySp(string constr, string commandText, SqlParameter[] commandParameters)
        {
            DataSet ds = SqlHelper.ExecuteDataset(constr, CommandType.StoredProcedure, commandText, commandParameters);
            ds.Locale = new System.Globalization.CultureInfo("zh-cn");
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteDatasetBySp(SqlConnection connection, string commandText, SqlParameter[] commandParameters)
        {
            DataSet ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, commandText, commandParameters);
            ds.Locale = new System.Globalization.CultureInfo("zh-cn");
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string constr, string commandText)
        {
            DataSet ds = SqlHelper.ExecuteDataset(constr, CommandType.Text, commandText);
            ds.Locale = new System.Globalization.CultureInfo("zh-cn");
            return ds;
        }
       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string constr, string commandText, SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(constr, CommandType.Text, commandText, commandParameters);
        }
     
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string constr, string commandText)
        {
            return SqlHelper.ExecuteNonQuery(constr, CommandType.Text, commandText);
        }
        #endregion

        #region ExecuteReader

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string constr, string commandText, SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(constr, CommandType.Text, commandText, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string constr, string commandText)
        {
            return SqlHelper.ExecuteReader(constr,CommandType.Text, commandText);
        }
        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string constr, string commandText, SqlParameter[] commandParameters)
        {
            try
            {
                return SqlHelper.ExecuteDataset(constr, CommandType.Text, commandText, commandParameters).Tables[0].Rows[0][0];
            }
            catch (Exception ex)
            {
                string ErrorMessage = ex.Message;
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string constr, string commandText)
        {
            try
            {
                return SqlHelper.ExecuteDataset(constr, CommandType.Text, commandText).Tables[0].Rows[0][0];
            }
            catch (Exception ex)
            {
                string ErrorMessage = ex.Message;
                return null;
            }
        }
        #endregion

 

  
    }

}